import pdb
import re
import os


def insert_list(student_list, cursor):
    for dic in student_list:
        insert(cursor, **dic)
        
def get_student_id_list(cursor, year):
    sql_statement = 'select student_id from qinghua where student_id like "%d%%"' % year
    try:
        cursor.execute(sql_statement)
    except Exception as e:
        pdb.set_trace() 
    results = cursor.fetchall()
    Ls = []
    for i in results:
        Ls.append(i[0])
    return Ls
    
def insert(cursor, **kwargs):
    # first check if student_id already exists    
    dic = kwargs
    if(dic.get('student_id') is None):
        raise Exception("No student id provided")
    sql_statement = 'insert ignore into qinghua('
    for i in dic.keys():
        sql_statement += i + ','
    sql_statement = sql_statement.rstrip(',')
    sql_statement += ') values ('
    for i in dic.values():
        sql_statement += '"%s",'%i
    sql_statement = sql_statement.rstrip(',')
    sql_statement += ')'
    try:
        cursor.execute(sql_statement)
    except Exception as e:
        pdb.set_trace()

def update(cursor, student_dic):
    '''
    update `qinghua` table from Python dictionary
    key `student_id` is required in student_dic
    '''
    global db    
    sql_statement = 'update qinghua set '
    for key,value in student_dic.items():
        if(type(value) is str):
            sql_statement += '{0} = \'{1}\','.format(key, value)
        else:
            sql_statement += '{0} = {1},'.format(key, value)
    sql_statement = sql_statement.rstrip(',')
    sql_statement += ' where student_id = {0}'.format(student_dic['student_id'])
    try:
        cursor.execute(sql_statement)
    except Exception as e:
        print(e)
        pdb.set_trace()
       
def select(cursor, student_id, fields):
    '''
    select specified fields from `qinghua` table,
    '''
    sql_statement = 'select '
    for field in fields:
            sql_statement += field + ','
    sql_statement = sql_statement.rstrip(',')
    sql_statement += ' from qinghua '
    sql_statement += 'where student_id = {0}'.format(student_id)
    try:
        cursor.execute(sql_statement)
    except Exception as e:
        pdb.set_trace() 
    return cursor.fetchall()   

def _select(cursor, conditions, fields):
    '''
    select specified fields from `qinghua` table,
    condition : dic
    fields: dic
    '''
    sql_statement = 'select '
    for field in fields:
            sql_statement += field + ','
    sql_statement = sql_statement.rstrip(',')
    sql_statement += ' from qinghua where '
    for k, v in conditions.items():
        sql_statement += '{0} = {1} and '.format(k, v)
    sql_statement = sql_statement.rstrip('and ')
    try:
        cursor.execute(sql_statement)
    except Exception as e:
        pdb.set_trace() 
    return cursor.fetchall()
    
# 荔园10-504C or 荷2-1111
def clean_dormitory(input_string):
    if input_string is None:
        return None
    input_string_inner = input_string.strip()
    if len(input_string_inner) == 0:
        return None
    if input_string_inner.find('荔') >= 0:
        return input_string_inner
    elif input_string_inner.find('荷') >= 0:
        find_obj = re.search('[1-5]', input_string_inner)
        if find_obj is not None:
            L_number = find_obj.group(0)
        elif input_string_inner.find('一'):
            L_number = '1'
        elif input_string_inner.find('二'):
            L_number = '2'
        elif input_string_inner.find('四'):
            L_number = '4'
        elif input_string_inner.find('五'):
            L_number = '5'
        else:
            print(input_string_inner)
            raise LookupError("not found")
        F_list = re.findall('[0-9]+', input_string_inner)
        if len(F_list) > 0:
            F_number = re.findall('[0-9]+', input_string_inner)[-1]
        else:
            return None
        return '荷' + L_number + '-' + F_number
        # focus to extract
    else:
        return None

def get_docx(dir):
    '''
    get docx files
    Parameters
    ----------
    dir: [string] directory to read docx files
    Returns
    -------
    out: list of docx files in the `dir`
    '''
    for i in os.listdir(dir):
        if i.find('docx') > 0:
            if(os.path.getsize(os.path.join(dir,i)) > 1024 * 502):#>0.5MB
                yield i   

def get_basic_info(dir_name, file_name):
    house_number_str = re.search('[1-5]', dir_name).group(0)
    re_obj = re.search('0([2-3])[0-9]{2}-([0-9]{2})([0-9]{2})', file_name)       
    start_floor_number = int(re_obj.group(1))
    end_floor_number = int(re_obj.group(2))
    floor_index_str = re_obj.group(3)
    return (house_number_str, start_floor_number, end_floor_number, floor_index_str)
    
def insert_or_update_teacher_info(cursor, name, mobile):
    global db
    teacher_dic = {'teacher_name' : name,
        'teacher_mobile': mobile
    }
    sql_statement = 'insert ignore into qinghua_teacher set '
    for key,value in teacher_dic.items():
        if(type(value) is str):
            sql_statement += '{0} = \'{1}\','.format(key, value)
        else:
            sql_statement += '{0} = {1},'.format(key, value)
    sql_statement = sql_statement.rstrip(',')
    try:
        cursor.execute(sql_statement)
    except Exception as e:
        print(e)
        pdb.set_trace()